import json
import mysql.connector
from mysql.connector import Error
from typing import List, Dict


def create_connection() -> mysql.connector.connection.MySQLConnection | None:
    """创建MySQL数据库连接"""
    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='root',
            password='root',
            database='test',
            port=3306,
            charset='utf8mb4'
        )
        print("数据库连接成功")
        return conn
    except Error as e:
        print(f"数据库连接失败: {e}")
        return None


def create_table(conn: mysql.connector.connection.MySQLConnection) -> None:
    """创建行政区划表"""
    create_table_sql = """
        CREATE TABLE IF NOT EXISTS sys_area (
            adcode VARCHAR(20) PRIMARY KEY,
            parent_adcode VARCHAR(20) NOT NULL,
            adcodes TEXT NOT NULL,
            citycode VARCHAR(20),
            name VARCHAR(100) NOT NULL,
            center_lat DECIMAL(10, 8),
            center_lnt DECIMAL(11, 8),
            level VARCHAR(20) NOT NULL,
            del_flag TINYINT DEFAULT 0,
            INDEX idx_parent_adcode (parent_adcode),
            INDEX idx_level (level)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
    try:
        cursor = conn.cursor()
        cursor.execute(create_table_sql)
        conn.commit()
        print("表创建成功")
    except Error as e:
        print(f"创建表失败: {e}")


def parse_center(center: str) -> tuple[float | None, float | None]:
    """解析坐标字段为经纬度"""
    try:
        if not center.strip():
            return None, None
        lnt_str, lat_str = center.split(',', 1)
        return float(lat_str), float(lnt_str)
    except (ValueError, IndexError):
        return None, None


def generate_unique_adcode(parent_adcode: str, level: str, index: int) -> str:
    """生成唯一ADCODE（街道级别自动递增编号）"""
    if level == 'street':
        return f"{parent_adcode}{index + 1:02d}"  # 街道级在父级后加两位序号
    return parent_adcode if level == 'province' else parent_adcode  # 其他级别使用原始或父级ADCODE


def convert_to_string(value) -> str:
    """将任意类型转换为字符串，特别处理列表"""
    if value is None:
        return ''
    if isinstance(value, list):
        return ','.join(map(str, value))
    return str(value).strip()


def process_hierarchy(data: List[Dict], parent_info: Dict = {}) -> List[Dict]:
    """递归处理层级数据"""
    records = []
    level_mapping = {
        'province': 1, 'city': 2, 'district': 3, 'street': 4
    }
    current_level = parent_info.get('level', 'province')
    parent_adcode = parent_info.get('adcode', '0')
    parent_path = parent_info.get('path', '0,')

    for idx, item in enumerate(data):
        # 基础字段校验与转换
        adcode = convert_to_string(item.get('adcode', ''))
        name = convert_to_string(item.get('name', '未知名称'))
        if not name:
            continue  # 跳过名称为空的记录

        # 处理坐标
        center = convert_to_string(item.get('center', ''))
        center_lat, center_lnt = parse_center(center)

        # 生成当前层级信息
        current_level = convert_to_string(item.get('level', 'unknown')).lower()
        if current_level not in level_mapping:
            current_level = 'unknown'

        # 生成唯一ADCODE（街道级别自动处理）
        if current_level == 'street' and adcode == parent_adcode:
            adcode = generate_unique_adcode(parent_adcode, current_level, idx)

        # 构建层级路径
        current_path = f"{parent_path}{adcode},"

        # 确保citycode是字符串
        citycode = convert_to_string(item.get('citycode', ''))

        # 构建记录
        record = {
            'adcode': adcode,
            'parent_adcode': parent_adcode,
            'adcodes': current_path,
            'citycode': citycode,
            'name': name,
            'center_lat': center_lat,
            'center_lnt': center_lnt,
            'level': current_level,
            'del_flag': 0
        }
        records.append(record)

        # 递归处理子层级
        if 'districts' in item and isinstance(item['districts'], list):
            child_records = process_hierarchy(
                item['districts'],
                {
                    'adcode': adcode,
                    'level': current_level,
                    'path': current_path
                }
            )
            records.extend(child_records)

    return records


def insert_data(conn: mysql.connector.connection.MySQLConnection, records: List[Dict]) -> None:
    """批量插入数据"""
    insert_sql = """
        INSERT INTO sys_area (
            adcode, parent_adcode, adcodes, citycode, name, 
            center_lat, center_lnt, level, del_flag
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE 
            name = VALUES(name), 
            center_lat = VALUES(center_lat), 
            center_lnt = VALUES(center_lnt)
    """
    try:
        cursor = conn.cursor()
        data_to_insert = [tuple(record.values()) for record in records]
        cursor.executemany(insert_sql, data_to_insert)
        conn.commit()
        print(f"成功插入/更新 {cursor.rowcount} 条记录")
    except Error as e:
        print(f"数据插入失败: {e}")
        # 打印导致错误的记录样本
        if cursor.rowcount > 0:
            print(f"已成功插入 {cursor.rowcount} 条记录，错误发生在后续数据")
            print("错误数据示例:", data_to_insert[cursor.rowcount])
        conn.rollback()


def main() -> None:
    """主处理流程"""
    conn = create_connection()
    if not conn:
        return

    try:
        create_table(conn)

        # 读取全量数据（请替换为实际文件路径）
        with open('geoinfo-all.json', 'r', encoding='utf-8') as f:
            all_data = json.load(f)

        # 处理顶级数据（省级）
        processed_records = []
        for province in all_data:
            if province.get('level') == 'province':
                province_records = process_hierarchy([province])
                processed_records.extend(province_records)

        # 插入数据库
        insert_data(conn, processed_records)

    except Exception as e:
        print(f"数据处理失败: {str(e)}")
    finally:
        if conn.is_connected():
            conn.close()
            print("数据库连接已关闭")


if __name__ == "__main__":
    main()